<?
//include all support file
include_once ("../modconfig.php");
include_once ("$ROOT_CLASS_DIR/class_database.php");
include_once ("$ROOT_CLASS_DIR/class_common.php");
include_once ("$ROOT_CLASS_DIR/class_login.php");

require_once('OLEwriter.php');
require_once('BIFFwriter.php');
require_once('Worksheet.php');
require_once('Workbook.php');

global $DBCoins,$DBGeneral,$DBConnection, $RS,$Index, $a, $b,$_CYear,$_CMonth,$_SMonth, $_LastMonth, $LMonth, $_LSMonth;


function HeaderingExcel($filename){
    header("Content-type:application/vnd.ms-excel");
    header("Content-Disposition:attachment;filename=$filename");
    header("Expires:0");
    header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
    header("Pragma: public");
}

//

$TableName = 'tblcoa';
$Month = GetParam("m","");
$Year = GetParam("y","");
$LYear = GetParam("ly","");
$LMonth = GetParam("lm","");
$LastMonth = GetParam("lm","");

if ($Month<10) $Month = "0".$Month;   
if (($Month-1)<10) {$LMonth="0".($Month-1);}
else $LMonth=$Month-1;
if ($LastMonth<10) $LastMonth = "0".$LastMonth;
$_CYear  = $Year;
$_CMonth = $Month;
$_SMonth = "aku".$Month;
$_LastMonth = "aku".$LastMonth;
$_LastYear = $LYear;
if ($Month==1) $_LSMonth = "awal";    
else $_LSMonth = "aku".$LMonth;    

$office=GetParam("office","");
$namafile = "Income_Statement".$office."_".$Month."_".$Year;;

//http headers
HeaderingExcel($namafile);

//make a workbook
$workbook=new Workbook("-");

//make first worksheet
$fJudul=& $workbook->add_format();
$fJudul->set_bold();
$fJudul->set_size(14);

$fJudulKolom=& $workbook->add_format();
$fJudulKolom->set_bold();
$fJudulKolom->set_size(8);
$fJudulKolom->set_align('centre');
$fJudulKolom->set_border(1);

$fBesar=& $workbook->add_format();
$fBesar->set_size(20);

$fKutipan=& $workbook->add_format();
$fKutipan->set_align('right');
$fKutipan->set_italic();
$fKutipan->set_size(8);
$fKutipan->set_color('blue');

$fList=& $workbook->add_format();
$fList->set_size(8);

$fIsi=& $workbook->add_format();
$fIsi->set_border(1);

$fIsiTengah=& $workbook->add_format();
$fIsiTengah->set_border(1);
$fIsiTengah->set_align('centre');


$angka=& $workbook->add_format();
$angka->set_num_format('#,###');
$angka->set_border(1);

$angka2=& $workbook->add_format();
$angka2->set_num_format('#,###.00');
$angka2->set_border(1);

$angkatengah=& $workbook->add_format();
$angkatengah->set_num_format('#,###');
$angkatengah->set_border(1);
$angkatengah->set_align('centre');

$tanggal=& $workbook->add_format();
$tanggal->set_num_format('D-MMM-YYYY');
$tanggal->set_border(1);

$worksheet1= & $workbook->add_worksheet("IncomeStatementJKT");
//seting zoom size
//75 is 75% zoom size
$worksheet1->set_zoom(100);

//set portrait page
$worksheet1->set_portrait(100);

//set a4 paper size
$worksheet1->set_paper(9);

//set hide gridlines
$worksheet1->hide_gridlines();

//set print area
$worksheet1->print_area(0,0,15,5);

//set page header
$worksheet1->set_header("header",$margin=2);


//set column
$worksheet1->set_column(1,1,30);
$worksheet1->set_column(2,2,15);
$worksheet1->set_column(3,3,15);
$worksheet1->set_column(4,4,30);
$worksheet1->set_column(5,5,15);
$worksheet1->set_column(6,6,15);
$worksheet1->set_column(7,7,30);
$worksheet1->set_column(8,8,30);
$worksheet1->set_column(9,9,13);
$worksheet1->set_column(10,10,4);
$worksheet1->set_column(11,11,25);
$worksheet1->set_column(12,12,10);
$worksheet1->set_column(13,13,10);
$worksheet1->set_column(14,14,9);
$worksheet1->set_column(15,15,9);
$worksheet1->set_column(16,16,9);
$worksheet1->set_column(17,17,15);




// buat koneksi database dan cari data
$DBConnection   = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);

//HEADER

$title = "INCOME STATEMENT";
$page   = 'HAL';
$title .= (GetParam("m","")=="13")?' (AUDIT)':"";
$perush = (GetParam("office",""))?GetCompany (GetParam("office","")):'PT. MASAJI TATANAN CONTAINER';


//Header
$worksheet1->write_string(1,1,$title,$fJudul);
$worksheet1->write_string(2,1,$perush,$fJudul);
$worksheet1->write_string(3,1,"AS OF: ".BuildPeriod (GetParam("y",""), GetParam("m","")),$fJudul);
$worksheet1->write_string(5,1,"DESKRIPSI",$fJudulKolom);
$worksheet1->write_string(5,2,"S/D BLN LALU",$fJudulKolom);
$worksheet1->write_string(5,3,"MUTASI",$fJudulKolom);
$worksheet1->write_string(5,4,"S/D BLN INI",$fJudulKolom);
$worksheet1->write_string(5,5,"BLN KE-".GetParam("lm","")." THN ".GetParam("ly",""),$fJudulKolom);

// Generate report
    
$SQL = "SELECT * FROM rpt_income_statement order by placeorder";
$MyResults = $DBConnection->dbc->get_results($SQL, ARRAY_A);
$a=0;
if ($MyResults) {
//die('dalam else $SQL='.$SQL);
    foreach ($MyResults as $row){
	$a=$a+1;
	$skada = $row["skada"];
	$currmonth=0;
	$lastmonth=0;
	$lastyearmonth=0;
	$mutasi=0;
	$WhereSQL='';
	if (strlen($skada)) {
        	$SkadaInRow = explode(",",$skada);
        	for ($x=0;$x<count($SkadaInRow);$x++) {
          		if (strlen($WhereSQL)) $WhereSQL .= " or a.skada like '{$SkadaInRow[$x]}%' ";
           	else $WhereSQL   .= " a.skada like '{$SkadaInRow[$x]}%' ";
        	}
		$SQL1 = "SELECT sum(b.{$_LastMonth}) as lastyearmonth,sum(a.{$_SMonth}) as currmonth,sum(a.{$_LSMonth}) as lastmonth 
          		FROM {$TableName} a
          		LEFT JOIN {$TableName} b ON a.skada=b.skada AND b.ledger={$_LastYear}  
          		WHERE a.ledger='{$_CYear}' AND ($WhereSQL) ";
		$RowResult = $DBConnection->dbc->get_row($SQL1);
		if ($row["inverse"]==1) {
			$currmonth=$RowResult->currmonth*-1;
			$lastmonth=$RowResult->lastmonth*-1;
			$mutasi= $currmonth - $lastmonth;
			$lastyearmonth=$RowResult->lastyearmonth*-1;
		} else {
			$currmonth=$RowResult->currmonth;
			$lastmonth=$RowResult->lastmonth;
			$mutasi= $currmonth - $lastmonth;
			$lastyearmonth=$RowResult->lastyearmonth;
		}

	} // strlen skada
	$worksheet1->write_string($a+6,1,$row["deskripsi"],$fIsi);
	$worksheet1->write_number($a+6,2,$lastmonth,$angka);
	$worksheet1->write_number($a+6,3,$mutasi,$angka);
	$worksheet1->write_number($a+6,4,$currmonth,$angka);
	$worksheet1->write_number($a+6,5,$lastyearmonth,$angka);
	//$worksheet1->write_string($a+6,6,$SQL1,$fIsi);


} //foreach
    
} // if 
function BuildPeriod ($year, $month) {
    $arraymonth = array("","JANUARI","FEBRUARI","MARET","APRIL","MEI","JUNI","JULI","AGUSTUS","SEPTEMBER","OKTOBER","NOPEMBER","DESEMBER");
    return strtoupper(date("d F Y", mktime(0, 0, 0, $month+1, 0, $year)));
  }


$workbook->close();
?>